
# first an ODBC connection to MSSQL database server has to be setup
# http://www.time-travellers.org/shane/howtos/MS-SQL-Express-Python-HOWTO.html
#  section Create the ODBC Data Source

import dbi
import odbc
import sys
import time
import string

dtnow_iso = lambda : time.strftime ("%Y-%m-%dT%H%M%S", time.localtime())
#2008-07-16T194418

zf9 = lambda x: string.zfill(str(x), 9)

x3x = lambda x: "%s.%s.%s" % (zf9(x)[:3], zf9(x)[3:6], zf9(x)[6:9])
# 2683099 -> 002.683.099

def main(odbc_conn_name, dbtype='mssql', debugmode=True):
    """ """
    #conn=odbc.odbc("DSN=dedus9009")
    conn=odbc.odbc("DSN=%s" % (odbc_conn_name))
    cursor=conn.cursor()
    o = {}
    id = 0
    dt = dtnow_iso()

    if dbtype == 'mssql':

        cursor.execute("select * from sys.databases")
        databases = cursor.fetchall()[:]

        for dbrec in databases:
            db = dbrec[0]
            if debugmode: print db
        
            #id += 1; oid = 'id' + dt + '_' + x3x(id)
            #o[oid] = {'db_name': db, 'tb_schema': '', 'tb_name': '', 'error': ''}
            if db in (   'master', 'model', 'backupmediaset'
                        , 'backupmediafamily''backupset', 'backupfile'
                        , 'restorehistory', 'restorefile', 'restorefilegroup'
                        , 'logmarkhistory', 'suspect_pages', 'msdb', 'tempdb'
                                ):
                id += 1; oid = '' + dt + '_' + x3x(id)
                o[oid] = {'db_name': db, 'tb_schema': None, 'tb_name': None, 'error': 'db_skipped'}
            else:
                try:
                    q = "use %s" % (db)
                    cursor.execute(q)
                    cursor.execute("""select * from information_schema.tables""")
                    tables = cursor.fetchall()[:]
                    for t in tables:
                        tb_schema = t[1]; tb_name = t[2]
                        if debugmode: print tb_schema, tb_name
                        if tb_schema +'.'+ tb_name in ('dbo.sysdiagrams'):
                            id += 1; oid = '' + dt + '_' + x3x(id)
                            o[oid] = {'db_name': db, 'tb_schema': t[1], 'tb_name': t[2], 'error': 'tb_skipped'}
                        else:
                            id += 1; oid = '' + dt + '_' + x3x(id)
                            o[oid] = {'db_name': db, 'tb_schema': t[1], 'tb_name': t[2], 'error': None}
                except:
                    id += 1; oid = '' + dt + '_' + x3x(id)
                    o[oid] =  {'db_name': db, 'tb_schema': None, 'tb_name': None, 'error': 'db error' + str(sys.exc_info()[:])}
        return o
    else:
        return o

def to_file(odbc_conn_name):
    ''' '''
    o = open('list_dbtb_' + dtnow_iso(), 'wb')

    ls = main(odbc_conn_name, dbtype='mssql', debugmode=False)
    for k in sorted(ls.keys()):
        o.write(str(ls[k]['db_name']) + '\t')
        o.write(str(ls[k]['tb_schema']) + '.' + str(ls[k]['tb_name']) + '\t')
        o.write(str(ls[k]['error']) + '\t')
        o.write(k + '\t')
        o.write('\x0D\x0A')
    o.close()

if __name__ == "__main__":
    ''' '''
    if  len(sys.argv) == 2:
        to_file(sys.argv[1])
        main(odbc_conn_name='localmssql') 
    else:
        print """
        list_structure.py <odbc_conn_name>

        example : list_structure xxxx

        odbc_conn_name (mandatory)
            odbc connection name (database server to list)
        """